package reg.data;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import reg.domain.Documento;


public class JDBCDocumentoDAO implements IDocumentoDAO {

	
	public List<Documento> seleccionaTodosLosDocumentos() {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		List<Documento> searchResults = new LinkedList<Documento>();
		ResultSet result = null;

		try {
			String sql = "SELECT * FROM Documentos";
			stmt = conn.prepareStatement(sql); 
			stmt.executeQuery();
			result = stmt.executeQuery();

			while (result.next()) {
				Documento d = new Documento();
				d.setNombre(result.getString("nombre"));
				d.setFecha(result.getDate("fecha"));
				d.setRuta(result.getString("ruta"));
				// TODO consulta sobre el usuario
				d.setSubidoPor(null);
				
				
				// /////////////////////////////////////////////////////
				// consultar pa obetener tags //
				// //////////////////////////////////////////////////////
				Connection conn4tags = ConnectionManager.getInstance().checkOut();
				PreparedStatement stmt4tags = null;
				//List<Documento> searchResults4tags = new LinkedList<Documento>();
				ResultSet result4tags = null;
				List<String> res = new ArrayList<String>();
				
				try {
					String sql4tags = "SELECT tag FROM Tags WHERE idDocumento = '" + result.getString("idDocumento") + "'";
					stmt4tags = conn4tags.prepareStatement(sql4tags); 
					stmt4tags.executeQuery();
					result4tags = stmt4tags.executeQuery();

					while (result4tags.next()) {
						res.add(result4tags.getString("tag"));

					}
					
				} catch (SQLException e) {
					System.out.println("Message: " + e.getMessage());
					System.out.println("SQLState: " + e.getSQLState());
					System.out.println("ErrorCode: " + e.getErrorCode());
				} finally {
					ConnectionManager.getInstance().checkIn(conn);
					try {
						if (result4tags != null)
							result4tags.close();
						if (stmt4tags != null)
							stmt4tags.close();
					} catch (SQLException e) {
					}

				}

				d.setTags(res);
				searchResults.add(d);
			}
			
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return searchResults;
	}

	
	
	@Override
	public List<Documento> seleccionaDocumuentosPorTag(String tag) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		List<Documento> searchResults = new LinkedList<Documento>();
		ResultSet result = null;

		try {
			String sql = "SELECT * FROM Documentos NATURAL JOIN Tags WHERE tag = '"	+ tag + "'";
			stmt = conn.prepareStatement(sql); 
			stmt.executeQuery();
			result = stmt.executeQuery();

			while (result.next()) {
				Documento d = new Documento();
				d.setNombre(result.getString("nombre"));
				d.setFecha(result.getDate("fecha"));
				d.setRuta(result.getString("ruta"));

				searchResults.add(d);
			}
			
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
		return searchResults;
	}

	
	
	@Override
	public Map<String, Integer> getNubeDeTags() {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null, stmt2 = null;
		List<String> searchResults = new LinkedList<String>();
		ResultSet result = null, result2 = null;
		Map<String, Integer> res = new HashMap<String, Integer>();
		
		try {
			String sql = "SELECT DISTINCT tag FROM Tags";
			stmt = conn.prepareStatement(sql); 
			stmt.executeQuery();
			result = stmt.executeQuery();

			while (result.next()) {
				searchResults.add(result.getString("tag"));
			}

			// //////////////////segunda consulta////////////////////////
			try {
				Connection conn2 = ConnectionManager.getInstance().checkOut();

				for (String str : searchResults) {
					String sql2 = "SELECT count(*)AS foo FROM Tags WHERE tag = '" + str + "' GROUP BY tag";
					stmt2 = conn2.prepareStatement(sql2); 
					stmt2.executeQuery();
					result2 = stmt2.executeQuery();
					result2.next();
					int foobar = result2.getInt("foo");// http://www.forosdelweb.com/f45/java-mysql-ayuda-con-select-count-323071/
					res.put(str, foobar);
				}
				
			} catch (SQLException e) {
				System.out.println("Message: " + e.getMessage());
				System.out.println("SQLState: " + e.getSQLState());
				System.out.println("ErrorCode: " + e.getErrorCode());
			} finally {
				ConnectionManager.getInstance().checkIn(conn);
				try {
					if (result2 != null)
						result2.close();
					if (stmt2 != null)
						stmt2.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return res;
	}

	
	
	public List<Documento> seleccionaDocumentosAPartirDeFecha(Date fechaUltimoLogin) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		List<Documento> searchResults = new LinkedList<Documento>();
		ResultSet result = null;

		try {
			String sql = "SELECT * FROM Documentos WHERE fecha > '"	+ fechaUltimoLogin + "'";
			stmt = conn.prepareStatement(sql);
			stmt.executeQuery();
			result = stmt.executeQuery();

			while (result.next()) {
				Documento d = new Documento();
				d.setNombre(result.getString("nombre"));
				d.setFecha(result.getDate("fecha"));
				d.setRuta(result.getString("ruta"));
				// TODO SetSubidoPor
				d.setSubidoPor(null);

				// /////////////////tags///////////////////////////7
				Connection conn4tags = ConnectionManager.getInstance().checkOut();
				PreparedStatement stmt4tags = null;
				//List<Documento> searchResults4tags = new LinkedList<Documento>();
				ResultSet result4tags = null;
				List<String> res = new ArrayList<String>();
				
				try {
					String sql4tags = "SELECT tag FROM Tags WHERE idDocumento = '"	+ result.getString("idDocumento") + "'";
					stmt4tags = conn4tags.prepareStatement(sql4tags); 
					stmt4tags.executeQuery();
					result4tags = stmt4tags.executeQuery();

					while (result4tags.next()) {
						res.add(result4tags.getString("tag"));
					}
					
				} catch (SQLException e) {
					System.out.println("Message: " + e.getMessage());
					System.out.println("SQLState: " + e.getSQLState());
					System.out.println("ErrorCode: " + e.getErrorCode());
				} finally {
					ConnectionManager.getInstance().checkIn(conn);
					try {
						if (result4tags != null)
							result4tags.close();
						if (stmt4tags != null)
							stmt4tags.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}

				d.setTags(res);
				searchResults.add(d);
				
			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return searchResults;
	}
	
	
	public Integer getNumeroDocsDesdeFecha(Date fecha)
	{
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
	//	List<Documento> searchResults = new LinkedList<Documento>();
		ResultSet result = null;
		Integer res = 0;
		try {
			String sql = "SELECT count(*) AS C FROM Documentos WHERE fecha > '"	+ fecha + "'";
			stmt = conn.prepareStatement(sql);
			stmt.executeQuery();
			result = stmt.executeQuery();
			result.next();
			res = result.getInt("C");
		}catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return res;

	}

}
